﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using DTO;
namespace DAO
{
    public class HangHoaDAO
    {
        public List<HangHoaDTO> LayHangHoa()
        {
            string sql = "SELECT * FROM HangHoa WHERE Deleted=0 ORDER BY SoTT";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader read = cmd.ExecuteReader();
                List<HangHoaDTO> listhh = new List<HangHoaDTO>();
                while (read.Read())
                { 
                    HangHoaDTO hh = new HangHoaDTO();
                    hh.MaHang = read.GetString(0);
                    hh.TenHang = read.GetString(1);
                    hh.MaLoaiHang = read.GetInt32(2);
                    hh.MaNCC = read.GetInt32(3);
                    hh.SoLuong = read.GetInt32(4);
                    listhh.Add(hh);
                }
                conn.Close();
                return listhh;
            }
        }
        public HangHoaDTO LayHangTheoMa(string MaHang)
        {
            string sql = "SELECT * FROM HangHoa WHERE MaHang = '"+MaHang+"'";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader read = cmd.ExecuteReader();
                read.Read();
                HangHoaDTO hh = new HangHoaDTO();
                hh.MaHang = read.GetString(0);
                hh.TenHang = read.GetString(1);
                hh.MaLoaiHang = read.GetInt32(2);
                hh.MaNCC = read.GetInt32(3);
                hh.SoLuong = read.GetInt32(4);
                conn.Close();
                return hh;
            }
        }
        public bool KTMaHang(string MaHang)
        {
            string sql = "SELECT COUNT(*) FROM HangHoa WHERE MaHang='"+MaHang+"'";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader read = cmd.ExecuteReader();
                read.Read();                
                int kq = read.GetInt32(0);
                conn.Close();
                if (kq == 0)
                    return true;
                return false;
            }

        }
        public bool ThemHangHoa(HangHoaDTO hh)
        {
            string sql = "INSERT INTO HangHoa(MaHang, TenHang, MaLoaiHang, MaNCC, SoLuong, Deleted)";
            sql += "VALUES('"+hh.MaHang+"',N'"+hh.TenHang+"', "+hh.MaLoaiHang+", "+hh.MaNCC+", "+hh.SoLuong+", 0)";
            //string sql1 = "Insert into ThongKe(MaHang,Ngay,SoLuong)";
            //sql1 += "values('"+hh.MaHang+"','" + DateTime.Now.ToShortDateString().ToString() + "'," + hh.SoLuong+")";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                int kq = cmd.ExecuteNonQuery();
                conn.Close();
                if (kq >= 0)
                    return true;                
                return false;
            }
        }
        public bool SuaHangHoa(HangHoaDTO hh)
        {
            string sql = "UPDATE HangHoa SET TenHang = N'"+hh.TenHang+"', MaLoaiHang = "+hh.MaLoaiHang+", ";
            sql+="MaNCC = "+hh.MaNCC+", SoLuong = "+hh.SoLuong+" WHERE MaHang = '"+hh.MaHang+"'";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                int kq = cmd.ExecuteNonQuery();
                conn.Close();
                if (kq >= 0)
                    return true;
                return false;
            }
        }
        public bool XoaHangHoa(string MaHang)
        {
            string sql = "UPDATE HangHoa SET Deleted = 1 WHERE MaHang = N'"+MaHang+"'";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                int kq = cmd.ExecuteNonQuery();
                conn.Close();
                if (kq >= 0)
                    return true;
                return false;
            }
        }
    }
}
